package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.common.constant.IncomeExpenseType
import com.opennews.openplatform.familyexpenseapi.common.constant.PaymentType
import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.BaseIncomeExpenseDetailDto
import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.CreateIncomeExpenseDetailDto
import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.IncomeExpenseDetailQueryArgument
import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.UpdateIncomeExpenseDetailDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.entity.tables.records.IncomeExpenseDetailRecord
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import com.opennews.openplatform.myspringbootcore.extension.isNotNullOrBlankOrLiteralNull
import com.opennews.openplatform.myspringbootcore.extension.isNullOrBlankOrLiteralNull
import jakarta.validation.Valid
import org.jooq.Condition
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.jooq.impl.DSL.countDistinct
import org.springframework.stereotype.Service
import java.math.BigDecimal
import java.time.LocalDateTime
import java.time.YearMonth

@Service
class IncomeExpenseDetailService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val bankService: BankService,
    private val incomeExpenseAnalysisService: IncomeExpenseAnalysisService,
    private val userService: UserService,
) {
    /**
     * Queries all income/expense records associated with the provided account group.
     *
     * @param queryArgument An instance of [IncomeExpenseDetailQueryArgument] containing the filtering criteria:
     * - `pageSize`: Number of records to return per page.
     * - `pageIndex`: Page index to fetch.
     * - `accountGroupId`: ID of the user's account group.
     * - `keyword`: A string that matches `IncomeExpenseDetail.details` and `IncomeExpenseDetail.remark` using SQL LIKE. Ignored if null or empty.
     * - `keywordsSearchCondition`: The search condition for multi-keyword search (OR or AND).
     * - `type`: Matches `IncomeExpenseDetail.type`. Ignored if null or empty.
     * - `detailType`: Matches `IncomeExpenseDetail.detailType`. Ignored if null or empty.
     * - `paymentType`: Matches `IncomeExpenseDetail.paymentType`. Ignored if null or empty.
     * - `bankCardId`: Matches `IncomeExpenseDetail.bankCard.id`. Ignored if null or empty.
     * - `startDate`: Must be less than or equal to `IncomeExpenseDetail.incomeExpenseDate`. Ignored if null.
     * - `endDate`: Must be greater than or equal to `IncomeExpenseDetail.incomeExpenseDate`. Ignored if null.
     *
     * @return A list of maps where each map contains:
     * - `id`: String — Income/expense detail ID
     * - `amount`: Float — Amount of the record
     * - `type`: String — Type of the record (income or expense)
     * - `detailType`: String — Category or description of the detail
     * - `paymentType`: String — How the payment was made
     * - `bankId`: String — ID of the bank (if paid by card)
     * - `bankShortName`: String — Short name of the bank (if paid by card)
     * - `bankLogoUrl`: String — Logo URL of the bank (if paid by card)
     * - `bankCardId`: String — ID of the bank card (if paid by card)
     * - `bankCardName`: String — Name of the bank card (if paid by card)
     * - `bankCardIsCreditCard`: Boolean — Whether the card is a credit card
     * - `details`: String — Additional detail of the transaction
     */

    fun queryListByAccountGroup(queryArgument: IncomeExpenseDetailQueryArgument): List<Map<String, Any?>> {
        // The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
        // If the start date and end date are both 2019-12-13 00:00:00, and we want to search the data between start and end date
        // we have to change the end date to 2019-12-14 00:00:00.
        // That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
        //if (queryArgument.endDate != null) {
        //    use(TimeCategory) {
        //        queryArgument.endDate = queryArgument.endDate + 1.day
        //    }
        //}

        val sharedCondition = getQueryByAccountGroupSharedCondition(queryArgument)

        return dslContext
            .select(
                INCOME_EXPENSE_DETAIL.ID.`as`("id"),
                INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`as`("incomeExpenseDate"),
                INCOME_EXPENSE_DETAIL.AMOUNT.`as`("amount"),
                INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`as`("detailType"),
                INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                BANK.ID.`as`("bankId"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK_CARD.ID.`as`("bankCardId"),
                BANK_CARD.NAME.`as`("bankCardName"),
                BANK_CARD.IS_CREDIT_CARD.`as`("bankCardIsCreditCard"),
                INCOME_EXPENSE_DETAIL.DETAILS.`as`("details"),
            )
            .from(INCOME_EXPENSE_DETAIL)
            .leftJoin(BANK_CARD).on(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
            .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
            .where(sharedCondition)
            .orderBy(
                INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.desc(),
                INCOME_EXPENSE_DETAIL.TYPE.desc(),
                INCOME_EXPENSE_DETAIL.BANK_CARD_ID.asc(),
                INCOME_EXPENSE_DETAIL.LAST_UPDATED.desc()
            )
            .limit(queryArgument.pageSize)
            .offset(queryArgument.pageIndex!! * queryArgument.pageSize!!)
            .fetch()
            .map { record ->
                val item = record.intoMap().toMutableMap()
                val shortName = item["bankShortName"] as String?
                item["bankLogoUrl"] = bankService.getLogoUrl(shortName)

                item
            }
    }

    /**
     * Queries the total amount of all income/expense records associated with the provided account group.
     *
     * @param queryArgument An instance of [IncomeExpenseDetailQueryArgument] containing the filtering criteria:
     * - `pageSize`: Number of records to return per page.
     * - `pageIndex`: Page index to fetch.
     * - `accountGroupId`: ID of the user's account group.
     * - `keyword`: A string that matches `IncomeExpenseDetail.details` and `IncomeExpenseDetail.remark` using SQL LIKE. Ignored if null or empty.
     * - `keywordsSearchCondition`: The search condition for multi-keyword search (OR or AND).
     * - `type`: Matches `IncomeExpenseDetail.type`. Ignored if null or empty.
     * - `detailType`: Matches `IncomeExpenseDetail.detailType`. Ignored if null or empty.
     * - `paymentType`: Matches `IncomeExpenseDetail.paymentType`. Ignored if null or empty.
     * - `bankCardId`: Matches `IncomeExpenseDetail.bankCard.id`. Ignored if null or empty.
     * - `startDate`: `IncomeExpenseDetail.incomeExpenseDate` must be greater than or equal to this value. Ignored if null.
     * - `endDate`: `IncomeExpenseDetail.incomeExpenseDate` must be less than or equal to this value. Ignored if null.
     *
     * @return The total amount as a numeric value.
     */

    fun queryTotalAmountByAccountGroup(queryArgument: IncomeExpenseDetailQueryArgument): BigDecimal {
        //// The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
        //// If the start date and end date are both 2019-12-13 00:00:00 and we want to search the data between start and end date
        //// we have to change the end date to 2019-12-14 00:00:00.
        //// That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
        //if (queryArgument.endDate != null) {
        //    use(TimeCategory) {
        //        queryArgument.endDate = queryArgument.endDate + 1.day
        //    }
        //}

        val shareCondition = getQueryByAccountGroupSharedCondition(queryArgument)

        val totalAmount = dslContext
            .select(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT))
            .from(INCOME_EXPENSE_DETAIL)
            .where(shareCondition)
            .fetchOneInto(BigDecimal::class.java)

        return totalAmount ?: BigDecimal.ZERO
    }

    /**
     * Queries which bank card IDs are used in income/expense records from a provided list.
     *
     * @param bankCardIds A list of bank card IDs to check.
     * @param accountGroupId The ID of the user's account group.
     * @return A list of bank card IDs that are used in income/expense records. Any unused IDs will be filtered out.
     */
    fun queryUsedBankCardIds(bankCardIds: List<String>, accountGroupId: String): List<String> {
        return if (bankCardIds.isNotEmpty()) {
            dslContext.selectDistinct(INCOME_EXPENSE_DETAIL.BANK_CARD_ID)
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.BANK_CARD_ID.`in`(bankCardIds)
                        .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                )
                .fetchInto(String::class.java)
        } else {
            emptyList()
        }
    }

    /**
     * Queries the income/expense list and day summary for account group by id.
     *
     * @param accountGroupId The string of the user account group id.
     * @param pageSize       How many records to return in one trip.
     * @param pageIndex      Where to take rows from.
     * @return The list of maps, each containing income/expense details,
     *         optionally with "monthSummary" and "daySummary" keys added.
     */
    fun queryListWithSummaryByAccountGroup(
        accountGroupId: String,
        pageSize: Int,
        pageIndex: Int,
    ): List<MutableMap<String, Any?>> {
        // Prepares the query argument
        val queryArgument = IncomeExpenseDetailQueryArgument().apply {
            this.accountGroupId = accountGroupId
            this.pageSize = pageSize
            this.pageIndex = pageIndex
        }

        // Queries the income/expense list
        val list = queryListByAccountGroup(queryArgument).map { it.toMutableMap() }

        // Gets date list from the detail list
        val dates = list.mapNotNull { it["incomeExpenseDate"] as? LocalDateTime }

        // Queries month summary based on the unique date list
        val monthSummaryList = incomeExpenseAnalysisService.queryMonthlySummaryList(accountGroupId, dates)

        // Adds monthSummary to the first item of each month
        for (summary in monthSummaryList) {
            val summaryDate = summary["incomeExpenseDate"] as? LocalDateTime ?: continue
            val month = YearMonth.from(summaryDate)

            val firstItem = list.firstOrNull {
                YearMonth.from(it["incomeExpenseDate"] as? LocalDateTime ?: return@firstOrNull false) == month
            }

            firstItem?.put("monthSummary", summary.toMutableMap())
        }

        // Queries day summary based on the unique date list
        val daySummaryList = incomeExpenseAnalysisService.queryDailySummaryList(accountGroupId, dates)

        // Adds daySummary to the first item of each date
        for (summary in daySummaryList) {
            val summaryDate = summary["incomeExpenseDate"]

            val firstItem = list.firstOrNull {
                it["incomeExpenseDate"] == summaryDate
            }

            firstItem?.put("daySummary", summary.toMutableMap())
        }

        return list
    }

    /**
     * Queries income expense details by its id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param id:             The string of the income expense detail id.
     * @return The map instance which contains keys below.
     * id: String of the income expense detail id.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * detailType: String of the detail type which indicates where the income from or the expense goes.
     * paymentType: String of the payment type which how the expense pay.
     * bankId: String of the bank id of the bank card if the expense is paid by card.
     * bankShortName: String of the short name of the bank card if the expense is paid by card.
     * bankCardId: String of the id of the bank card if the expense is paid by card.
     * bankCardName: String of the name of the bank card if the expense is paid by card.
     * bankCardEnabled: Bool to indicate if this card is enabled.
     * details: String of the income or expense detail.
     * remark: String of the necessary extra remark.
     * latitude: Float of location latitude.
     * longitude: Float of location longitude.
     * address: String of location address.
     * formattedAddress: String of location formatted address.
     * province: String of location province.
     * city: String of location city.
     * district: String of location district.
     * createdBy: String of the username of who created this item.
     * updatedBy: String of the username of who updated this item.
     * dateCreated: Date of when this detail was created.
     * lastUpdated: Date of when this detail was updated.
     */
    fun queryDetails(accountGroupId: String, id: String): Map<String, Any?> {
        // Queries detail by account group id.
        var result: Map<String, Any?>? = dslContext
            .select(
                INCOME_EXPENSE_DETAIL.ID.`as`("id"),
                INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`as`("incomeExpenseDate"),
                INCOME_EXPENSE_DETAIL.AMOUNT.`as`("amount"),
                INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`as`("detailType"),
                INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                BANK.ID.`as`("bankId"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK_CARD.ID.`as`("bankCardId"),
                BANK_CARD.NAME.`as`("bankCardName"),
                BANK_CARD.IS_CREDIT_CARD.`as`("bankCardIsCreditCard"),
                INCOME_EXPENSE_DETAIL.DETAILS.`as`("details"),
                INCOME_EXPENSE_DETAIL.REMARK.`as`("remark"),
                INCOME_EXPENSE_DETAIL.LATITUDE.`as`("latitude"),
                INCOME_EXPENSE_DETAIL.LONGITUDE.`as`("longitude"),
                INCOME_EXPENSE_DETAIL.ADDRESS.`as`("address"),
                INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.`as`("formattedAddress"),
                INCOME_EXPENSE_DETAIL.PROVINCE.`as`("province"),
                INCOME_EXPENSE_DETAIL.CITY.`as`("city"),
                INCOME_EXPENSE_DETAIL.DISTRICT.`as`("district"),
                INCOME_EXPENSE_DETAIL.CREATED_BY.`as`("createdBy"),
                INCOME_EXPENSE_DETAIL.UPDATED_BY.`as`("updatedBy"),
                INCOME_EXPENSE_DETAIL.DATE_CREATED.`as`("dateCreated"),
                INCOME_EXPENSE_DETAIL.LAST_UPDATED.`as`("lastUpdated"),
            )
            .from(INCOME_EXPENSE_DETAIL)
            .leftJoin(BANK_CARD).on(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
            .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
            .where(
                INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL.ID.eq(id))
            )
            .fetchOneMap()

        if (result == null) {
            return emptyMap()
        }

        val createdBy = result["createdBy"].toString()
        val updatedBy = result["updatedBy"].toString()

        // Queries createdBy & updatedBy basic info.
        val userDetails = userService.queryBasicInfo(
            accountGroupId,
            listOf(createdBy, updatedBy),
        )

        result = result.toMutableMap().apply {
            put("createdBy", userDetails.firstOrNull { it["username"] == createdBy } ?: emptyMap<String, Any>())
            put("updatedBy", userDetails.firstOrNull { it["username"] == updatedBy } ?: emptyMap<String, Any>())

            // Sets the bank logo url based on its short name.
            put("bankLogoUrl", bankService.getLogoUrl(this["bankShortName"] as String?))
        }

        return result
    }

    /**
     * Queries the specific credit card bill based on bank card id, bill year, and bill month.
     * <p>
     * We don't generate the bill at this moment by using CreditCardBill and CreditCardBillExpenseDetail because that
     * would introduce more complex logic and effort.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records is return in one trip.
     * @param pageIndex:      Where to take rows.
     * @param bankCardId:     The String of bank card id.
     * @param statementDate:  The statement date of bill.
     * @return The return result of queryListByAccountGroup method.
     */
    fun queryCreditCardBill(
        accountGroupId: String,
        pageSize: Int,
        pageIndex: Int,
        bankCardId: String,
        statementDate: LocalDateTime,
    ): List<Map<String, Any?>> {
        // Queries the statement date of the credit card with its id, statement year and month.
        val cardStatementDateRange = createCreditCardBillStatementDateRange(statementDate)

        // Prepares the query argument.
        val queryArgument = IncomeExpenseDetailQueryArgument().apply {
            this.accountGroupId = accountGroupId
            this.bankCardId = bankCardId
            this.pageSize = pageSize
            this.pageIndex = pageIndex
            this.startDate = cardStatementDateRange["statementStartDate"]
            this.endDate = cardStatementDateRange["statementEndDate"]
        }

        // Queries the list which matches the date and bank card.
        return queryListByAccountGroup(queryArgument)
    }

    /**
     * Queries the credit card bill total amount.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param bankCardId:     The string of bank card id.
     * @param statementDate:  The statement date of bill.
     * @return The BigDecimal of the total amount.
     */
    fun queryCreditCardBillAmount(
        accountGroupId: String,
        bankCardId: String,
        statementDate: LocalDateTime,
    ): BigDecimal {
        // Queries the statement date of the credit card with its id, statement year and month.
        val cardStatementDateRange = createCreditCardBillStatementDateRange(statementDate)

        // Prepares the query argument.
        val queryArgument = IncomeExpenseDetailQueryArgument().apply {
            this.accountGroupId = accountGroupId
            this.bankCardId = bankCardId
            this.startDate = cardStatementDateRange["statementStartDate"]
            this.endDate = cardStatementDateRange["statementEndDate"]
        }

        // Queries the total amount within the card statement date range.
        return queryTotalAmountByAccountGroup(queryArgument)
    }

    /**
     * Queries basic statistics including totalIncomeExpenseCount and totalIncomeExpenseDays.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The map instance which contains keys below.
     * totalIncomeExpenseCount: Int of total income/expense recorded.
     * totalIncomeExpenseDays: Int of total income/expense days recorded.
     * totalUserCount: Int of total account group users.
     */
    fun queryBasicStatistics(accountGroupId: String): Map<String, Any> {
        val totalUserCount =
            dslContextWrapper.count(ACCOUNT_GROUP_USER, ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId))

        val totalIncomeExpenseCount =
            dslContextWrapper.count(INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))

        val totalBankCardCount = dslContextWrapper.count(BANK_CARD, BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId))

        val totalIncomeExpenseDays = dslContext.select(countDistinct(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
            .from(INCOME_EXPENSE_DETAIL)
            .where(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .fetchOneInto(Integer::class.java)

        return mapOf(
            "totalUserCount" to totalUserCount,
            "totalBankCardCount" to totalBankCardCount,
            "totalIncomeExpenseCount" to totalIncomeExpenseCount,
            "totalIncomeExpenseDays" to (totalIncomeExpenseDays ?: 0)
        )
    }

    /**
     * Queries coordinates for the specified account group and year.
     *
     * @param accountGroupId: The string of user account group id.
     * @param year:            The string of year to filter records by.
     * @return A list of maps where each map represents a record containing formatted address, latitude, and longitude.
     */
    fun queryCoordinates(accountGroupId: String, year: String?): List<Map<String, Any>> {
        // Locations contain same formatted address but different coordinates which we like to take only one match.
        // https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
        val subquery = dslContext.select(DSL.max(INCOME_EXPENSE_DETAIL.ID))
            .from(INCOME_EXPENSE_DETAIL)
            .where(
                INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(IncomeExpenseType.EXPENSE))
                    .and(INCOME_EXPENSE_DETAIL.LATITUDE.isNotNull)
                    .and(INCOME_EXPENSE_DETAIL.LONGITUDE.isNotNull)
                    .and(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.isNotNull)
                    .and(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.ne(""))
                    .and(
                        if (year.isNullOrBlankOrLiteralNull()) {
                            DSL.trueCondition()
                        } else {
                            DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year!!.toInt())
                        }
                    )
            )
            .groupBy(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS)

        return dslContext
            .select(
                INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.`as`("formattedAddress"),
                INCOME_EXPENSE_DETAIL.LATITUDE.`as`("latitude"),
                INCOME_EXPENSE_DETAIL.LONGITUDE.`as`("longitude"),
            )
            .from(INCOME_EXPENSE_DETAIL)
            .where(INCOME_EXPENSE_DETAIL.ID.`in`(subquery))
            .fetchMaps()
    }

    /**
     * Adds a new record of IncomeExpenseDetail with posted data.
     * The username used to query the account group id and saved to createdBy and updatedBy.
     *
     * @param accountGroupId: The string of user account group id.
     * @param username:       The string of username which is saved to createdBy and updatedBy.
     * @param data:           The map instance which contains the keys with same names as IncomeExpenseDetail properties.
     */
    fun add(accountGroupId: String, username: String, @Valid data: CreateIncomeExpenseDetailDto) {
        // Creates IncomeExpenseDetail based on dto.
        val record = createRecord(data, username)

        // Associates with account group.
        record.accountGroupId = accountGroupId

        // Specifies creator.
        record.createdBy = username

        // Saves change.
        record.insert()
    }

    /**
     * Updates existing IncomeExpenseDetail with posted data.
     *
     * @param accountGroupId: The string of user account group id.
     * @param username:       The string of username which saved to updatedBy.
     * @param data:           The map instance which contains the keys with same names as IncomeExpenseDetail properties.
     */
    fun update(accountGroupId: String, username: String, @Valid data: UpdateIncomeExpenseDetailDto) {
        // Just makes sure only updates owned data.
        if (dslContextWrapper.exists(
                INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ID.eq(data.id)
                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            )
        ) {
            val record = createRecord(data, username)
            record.changed(INCOME_EXPENSE_DETAIL.ID, false)

            // Specifies updater.
            record.updatedBy = username

            record.update()
        }
    }

    /**
     * Deletes the IncomeExpenseDetails with provided list of ids.
     *
     * @param accountGroupId: The string of user account group id.
     * @param ids:             The string list of ids.
     */
    fun delete(accountGroupId: String, ids: List<String>) {
        if (ids.isNotEmpty()) {
            // Executes batch deleting by DetachedCriteria.
            dslContext.deleteFrom(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.ID.`in`(ids)
                        .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                )
                .execute()
        }
    }

    /**
     * Creates IncomeExpenseDetailRecord based on BaseIncomeExpenseDetailDto.
     *
     * @param data: The BaseIncomeExpenseDetailDto instance holding data.
     * @param username: The username to save to updatedBy.
     */
    private fun createRecord(@Valid data: BaseIncomeExpenseDetailDto, username: String): IncomeExpenseDetailRecord {
        val record = dslContext.newRecord(INCOME_EXPENSE_DETAIL)

        // Updates the bank card property.
        record.bankCardId = data.bankCard?.id?.asString()

        // Updates all properties which data contains.
        record.from(data)

        // Specifies updater.
        record.updatedBy = username

        // Sets the default payment type if needed.
        if (record.paymentType.isNullOrBlankOrLiteralNull()) {
            record.paymentType = PaymentType.CASH
        }

        // Just in case user inputs negative number for income and positive number for expense.
        // We take the absolute value first.
        record.amount = record.amount.abs()

        // If it is expense we take it as negative number.
        if (record.type == IncomeExpenseType.EXPENSE) {
            record.amount = record.amount.negate()
        }

        return record
    }

    /**
     * Creates the shared delegate closure for criteria usage. Especially for queryListByAccountGroup and queryTotalAmountByAccountGroup methods.
     *
     * @param queryArgument: The instance of IncomeExpenseDetailQueryArgument which holds criteria. It contains the following properties:
     *                       pageSize: How many records are returned in one trip.
     *                       pageIndex: Where to take rows.
     *                       accountGroupId: The user's account group id.
     *                       keyword: The String which matches IncomeExpenseDetail.details, IncomeExpenseDetail.remark and IncomeExpenseDetail.formattedAddress properties as SQL LIKE operation. It will be ignored when value is null or empty.
     *                       keywordsSearchCondition: The search condition for multi-keyword. The condition is OR or AND.
     *                       type: The String which matches IncomeExpenseDetail.type property. It will be ignored when value is null or empty.
     *                       detailType: The String which matches IncomeExpenseDetail.detailType property. It will be ignored when value is null or empty.
     *                       paymentType: The String which matches IncomeExpenseDetail.paymentType property. It will be ignored when value is null or empty.
     *                       bankCardId: The String which matches IncomeExpenseDetail.bankCard.id property. It will be ignored when value is null or empty.
     *                       startDate: IncomeExpenseDetail.incomeExpenseDate property must be greater than or equal to this Date value. It will be ignored when value is null.
     *                       endDate: IncomeExpenseDetail.incomeExpenseDate property must be less than or equal to this Date value. It will be ignored when value is null.
     * @return The Condition object.
     */
    private fun getQueryByAccountGroupSharedCondition(queryArgument: IncomeExpenseDetailQueryArgument): Condition {
        // The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
        // If the start date and end date are both 2019-12-13 00:00:00 and we want to search the data between start and end date
        // we have to change the end date to 2019-12-14 00:00:00.
        // That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
        queryArgument.endDate?.let {
            queryArgument.endDate = it.plusDays(1)
        }

        var condition: Condition = INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(queryArgument.accountGroupId)

        queryArgument.startDate?.let {
            // Where a property is greater than or equal to a particular value.
            condition = condition.and(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.ge(it))
        }

        queryArgument.endDate?.let {
            // Where a property is less than a particular value.
            condition = condition.and(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.lt(it))
        }

        if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
            val keywords = queryArgument.keyword!!.split("\\s+".toRegex())
            var keywordCondition: Condition = DSL.noCondition()

            // Determines the search logic (AND vs OR) based on user input.
            val isAndCondition =
                queryArgument.keywordsSearchCondition == IncomeExpenseDetailQueryArgument.KEYWORDS_SEARCH_AND_CONDITION

            for (keyword in keywords) {
                // For each keyword, create the condition for matching in `details`, `remark`, and `formatted_address`
                val keywordMatch = INCOME_EXPENSE_DETAIL.DETAILS.like("%$keyword%")
                    .or(INCOME_EXPENSE_DETAIL.REMARK.like("%$keyword%"))
                    .or(INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.like("%$keyword%"))

                keywordCondition = if (isAndCondition) {
                    // If it's an AND condition, we need to ensure that all fields match the keyword.
                    keywordCondition.and(keywordMatch)
                } else {
                    // If it's an OR condition, we add the keyword condition to the OR group.
                    keywordCondition.or(keywordMatch)
                }
            }

            condition = condition.and(keywordCondition)
        }

        queryArgument.type?.takeIf { it.isNotNullOrBlankOrLiteralNull() }?.let {
            condition = condition.and(INCOME_EXPENSE_DETAIL.TYPE.eq(it))
        }

        queryArgument.detailType?.takeIf { it.isNotNullOrBlankOrLiteralNull() }?.let {
            condition = condition.and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(it))
        }

        queryArgument.paymentType?.takeIf { it.isNotNullOrBlankOrLiteralNull() }?.let {
            condition = condition.and(INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(it))
        }

        queryArgument.bankCardId?.takeIf { it.isNotNullOrBlankOrLiteralNull() }?.let {
            condition = condition.and(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(it))
        }

        return condition
    }

    /**
     * Creates the credit card statement date range.
     *
     * @param statementDate: The Date of this month's statement date.
     * @return The map instance which contains keys below.
     * statementStartDate: The start date of the statement date.
     * statementEndDate: The end date of the statement date.
     */
    private fun createCreditCardBillStatementDateRange(statementDate: LocalDateTime): Map<String, LocalDateTime> {
        return mapOf(
            "statementStartDate" to statementDate.minusMonths(1),
            "statementEndDate" to statementDate.minusDays(1)
        )
    }
}